﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Common;
using Musicman.Common.Entities;
using System.Data.Common;

namespace Musicman.DAL
{
    #region helper classes

    public class SelectParameter
    {
        private string parameterName;
        private DbType dbType;
        private object value;

        public string ParameterName
        {
            get { return parameterName; }
        }

        public DbType DbType
        {
            get { return dbType; }
        }

        public object Value
        {
            get { return this.value; }
        }

        public SelectParameter(string name, DbType type, object value)
        {
            this.parameterName = name;
            this.dbType = type;
            this.value = value;
        }

    }
    #endregion

    public class SearchFileDAO : DAOBase
    {
        # region singleton definition
        static readonly SearchFileDAO instance = new SearchFileDAO();

        public static SearchFileDAO Instance
        {
            get
            {
                return instance;
            }
        }
        static SearchFileDAO()
        {
        }
        # endregion

        #region select
        private string selectSQL = @"SELECT {0} SearchFile.SearchFileID, SearchFile.Name, 
                                                      SearchFile.ChangeTime, SearchFile.Length, 
                                                      SearchFile.FilePath, SearchFile.SearchJobID, 
                                                      SearchFile.PresentSequence, 
                                                      SearchFile.Visited
                                                   {1} 
                                                   FROM SearchFile {2} {3}";

        public List<SearchFile> LoadSearchfiles(string where, string orderby, IEnumerable<SelectParameter> parameters)
        {
            return LoadSearchfiles(0, where, "", orderby, parameters);
        }

        public List<SearchFile> LoadSearchfiles(int top, string where, string additional, string orderby, IEnumerable<SelectParameter> parameters)
        {
            List<SearchFile> res = new List<SearchFile>();
            string strTop = top == 0 ? "" : String.Format(" TOP {0}", top );
            string sql = String.Format(selectSQL, strTop, additional, where, orderby);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);

                foreach (SelectParameter parameter in parameters)
                {
                    db.AddInParameter(cmd, parameter.ParameterName, parameter.DbType, parameter.Value);
                }
                
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        SearchFile sf = CreateFile(reader);
                        res.Add(sf);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res; 
        }

        public SearchFile CreateFile(IDataReader reader)
        {
            SearchFile res = new SearchFile();
            res.SearchFileID = reader.GetInt64(0);
            res.Name = reader.GetString(1);
            res.ChangeTime = reader.GetDateTime(2);
            res.Length= reader.GetInt64(3);
            res.FilePath = reader.GetString(4);
            res.SearchJobID = reader.GetInt64(5);
            res.PresentSequence = reader.GetInt32(6);
            res.Visited = reader.GetBoolean(7);
            return res;
        }

        public FileStats CreateFileStats(IDataReader reader, int baseIndex)
        {
            FileStats res = new FileStats();
            res.SearchFileID =  reader.GetInt64(baseIndex + 0);
            res.TotalDownloads = reader.GetInt32(baseIndex + 1);
            res.TotalVoted = reader.GetInt32(baseIndex + 2);
            res.Rating = reader.GetDecimal(baseIndex + 3);
            return res;
        }

        public FileStats GetFileStats(long searchFileId)
        {
            FileStats res = null;
            string sql = String.Format("SELECT SearchFileID,TotalDownloads,TotalVoted,Rating FROM dbo.FilesStats WHERE SearchFileID = {0}", searchFileId);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);
                reader = db.ExecuteReader(cmd);
                res = new FileStats();
                if (reader != null)
                {
                    if (reader.Read())
                    {
                        res = CreateFileStats(reader, 0);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }
        #endregion

        #region insert

        private DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSql = @"INSERT INTO dbo.SearchFile (Name,ChangeTime,Length,FilePath,SearchJobID,PresentSequence,Visited)
                                                     VALUES (@Name,@ChangeTime,@Length,@FilePath,@SearchJobID,@PresentSequence,@Visited) 
                                    SET @SearchFileID = SCOPE_IDENTITY()";
            DbCommand cmd = db.GetSqlStringCommand(insertSql);
            db.AddInParameter(cmd, "@Name", DbType.String);
            db.AddInParameter(cmd, "@ChangeTime", DbType.DateTime);
            db.AddInParameter(cmd, "@Length", DbType.Int64);
            db.AddInParameter(cmd, "@FilePath", DbType.String);
            db.AddInParameter(cmd, "@SearchJobID", DbType.Int64);
            db.AddInParameter(cmd, "@PresentSequence", DbType.Int32);
            db.AddInParameter(cmd, "@Visited", DbType.Boolean);
            db.AddOutParameter(cmd, "@SearchFileID", DbType.Int64, 8);
            return cmd;
        }

        private void SetInsertParameters(SqlDatabase db, DbCommand cmd, SearchFile sf)
        {
            db.SetParameterValue(cmd, "@Name", sf.Name);
            db.SetParameterValue(cmd, "@ChangeTime", sf.ChangeTime);
            db.SetParameterValue(cmd, "@Length", sf.Length);
            db.SetParameterValue(cmd, "@FilePath", sf.FilePath);
            db.SetParameterValue(cmd, "@SearchJobID", sf.SearchJobID);
            db.SetParameterValue(cmd, "@PresentSequence", sf.PresentSequence);
            db.SetParameterValue(cmd, "@Visited", sf.Visited);
        }

        public void InsertSearchFileList(List<SearchFile> files, SqlDatabase db)
        {
            if (files.Count > 0)
            {
                DbCommand cmd = GetInsertCommand(db);
                for (int i = 0; i < files.Count; i++)
                {
                    SearchFile sf = files[i];
                    SetInsertParameters(db, cmd, sf);
                    db.ExecuteNonQuery(cmd);
                }
            }
        }

        public void AddToPendingDownloads(long personID, List<long>files)
        {
            string insertCmd = "dbo.AddPendingDownload";
            DbTransaction tranaction = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetStoredProcCommand(insertCmd);
                conn = db.CreateConnection();
                conn.Open();
                tranaction = conn.BeginTransaction();

                db.AddInParameter(cmd, "@UserID", DbType.Int64, personID);
                db.AddInParameter(cmd, "@SearchFileID", DbType.Int64);
                for (int i = 0; i < files.Count; i++)
                {
                    db.SetParameterValue(cmd, "@SearchFileID", files[i]);
                    db.ExecuteNonQuery(cmd, tranaction);
                }
                tranaction.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                if (tranaction != null) { tranaction.Rollback(); }
                if (conn != null) { conn.Close(); }
                throw ex;
            }
        }

        #endregion

        #region update

        private DbCommand GetUpdateCommand(SqlDatabase db)
        {
            string updateSql = @"UPDATE dbo.SearchFile SET PresentSequence = @PresentSequence, Visited = @Visited
                                    WHERE SearchFileID = @SearchFileID";
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@PresentSequence", DbType.Int32);
            db.AddInParameter(cmd, "@Visited", DbType.Boolean);
            db.AddInParameter(cmd, "@SearchFileID", DbType.Int64);
            return cmd;
        }

        private void SetUpdateParameters(SqlDatabase db, DbCommand cmd, SearchFile sf)
        {
            db.SetParameterValue(cmd, "@PresentSequence", sf.PresentSequence);
            db.SetParameterValue(cmd, "@Visited", sf.Visited);
            db.SetParameterValue(cmd, "@SearchFileID", sf.SearchFileID);
        }


        public void UpdateSearchFileList(List<SearchFile> files, SqlDatabase db)
        {
            if (files.Count > 0)
            {
                DbCommand cmd = GetUpdateCommand(db);
                for (int i = 0; i < files.Count; i++)
                {
                    SearchFile sf = files[i];
                    SetUpdateParameters(db, cmd, sf);
                    db.ExecuteNonQuery(cmd);
                }
            }
        }

        public void SetVisited(long jobID, bool visited)
        {
            string sql = "UPDATE SearchFile SET visited = @VISITED WHERE SearchJobID = @SEARCHJOBID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@VISITED", DbType.Boolean, visited);
            db.AddInParameter(cmd, "@SEARCHJOBID", DbType.Int64, jobID);
            db.ExecuteNonQuery(cmd);
        }

        public void IncreasePresentSequence(long jobID, SqlDatabase db)
        {
            string sql = "UPDATE SearchFile SET PresentSequence = PresentSequence + 1, Visited = 1 WHERE SearchJobID = @SEARCHJOBID AND Visited = 0";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@SEARCHJOBID", DbType.Int64, jobID);
            db.ExecuteNonQuery(cmd);
        }

        public bool UpdateDownloadStats(long personID, long searchFileID, long fileLength, string fileName, int count)
        {
            bool res = false;
            string spName = "dbo.UpdateDownloadStats";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetStoredProcCommand(spName);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, personID);
            db.AddInParameter(cmd, "@SEARCHFILEID", DbType.Int64, searchFileID > 0 ? (object)searchFileID : DBNull.Value);
            db.AddInParameter(cmd, "@FILELENGHT", DbType.Int64, fileLength);
            db.AddInParameter(cmd, "@FILENAME", DbType.String, fileName);
            db.AddInParameter(cmd, "@NUM", DbType.Int32, count);
            res = db.ExecuteNonQuery(cmd) == 1;
            return res;
        }

        public bool UpdateFileStats(long searchFileID, int rating)
        {
            bool res = false;
            string spName = "dbo.UpdateFileStats";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetStoredProcCommand(spName);
            db.AddInParameter(cmd, "@SearchFileID", DbType.Int64, searchFileID);
            db.AddInParameter(cmd, "@mark", DbType.Int32, rating);
            res = db.ExecuteNonQuery(cmd) == 1;
            return res;
        }
        #endregion

        #region delete

        private DbCommand GetDeleteCommand(SqlDatabase db)
        {
            string deleteSql = @"DELETE FROM dbo.SearchFile WHERE SearchFileID = @SearchFileID";
            DbCommand cmd = db.GetSqlStringCommand(deleteSql);
            db.AddInParameter(cmd, "@SearchFileID", DbType.Int64);
            return cmd;
        }

        private void SetDeleteParameters(SqlDatabase db, DbCommand cmd, SearchFile sf)
        {
            db.SetParameterValue(cmd, "@SearchFileID", sf.SearchFileID);
        }

        public void DeleteSearchFileList(List<SearchFile> files, SqlDatabase db)
        {
            if (files.Count > 0)
            {
                DbCommand cmd = GetDeleteCommand(db);
                for (int i = 0; i < files.Count; i++)
                {
                    SearchFile sf = files[i];
                    SetDeleteParameters(db, cmd, sf);
                    db.ExecuteNonQuery(cmd);
                }
            }
        }

        public int DeleteIdleFiles(long jobID, int deleteAfter, SqlDatabase db)
        {
            string commandName = "dbo.DeleteSearchFile";
            DbCommand cmd = db.GetStoredProcCommand(commandName);
            db.AddInParameter(cmd, "@SEARCHFILEID", DbType.Int64, DBNull.Value);
            db.AddInParameter(cmd, "@SEARCHJOBID", DbType.Int64, jobID);
            db.AddInParameter(cmd, "@DELETEAFTERSEQ", DbType.Int32, deleteAfter);
            int res = db.ExecuteNonQuery(cmd);
            return res;
        }

        public void RemoveFromPendingDownloads(long personID, List<long> files)
        {
            string deleteSql = "DELETE FROM dbo.UserDownloads WHERE userid = @USERID AND SearchFielId = @SEARCHFILEID";
            DbTransaction tranaction = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(deleteSql);
                conn = db.CreateConnection();
                conn.Open();
                tranaction = conn.BeginTransaction();

                db.AddInParameter(cmd, "@USERID", DbType.Int64, personID);
                db.AddInParameter(cmd, "@SEARCHFILEID", DbType.Int64);
                for (int i = 0; i < files.Count; i++)
                {
                    db.SetParameterValue(cmd, "@SEARCHFILEID", files[i]);
                    db.ExecuteNonQuery(cmd, tranaction);
                }
                tranaction.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                if (tranaction != null) { tranaction.Rollback(); }
                if (conn != null) { conn.Close(); }
                throw ex;
            }
        }

        #endregion
    }
}
